SQL Server
Summary
This document covers the information to gather from Azure SQL or SQL Server in order to configure a Qarbine data service. The data service will use the Qarbine Microsoft_SQL driver. You can define multiple data services that access the same SQL endpoint though with varying credentials. Once a data service is defined, you can manage which Qarbine principals have access to it and its associated Azure SQL or SQL Server data. A Qarbine administrator has visibility to all data services.
Overview
To connect to the SQL Server the following are needed:
- qualified server name,
- user account and password and
- optional default database.
For some further information see
Use Node.js to query a database - Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn
Gather SQL Information
Server Name
Navigate to the SQL Databases
Select the database of interest.
On the right side copy the server name into a temporary location.
This value will be used as the Qarbine server template.
Network Access
Verify network access by first clicking on the highlighted tab shown below.
Adjust the firewall rules as necessary such as allowing certain IP addresses. If changes are made then be sure the Save them.
Click the back button in the browser to return to the main SQL Database page.
User Account
Identify the user account and password for Qarbine to connect with. Preferably this is a readonly account. Optionally decide on the default database.
Qarbine Configuration
Compute Node Preparation
Determine which compute node service endpoint you want to run this data access from. That URL will go into the Data Service’s Compute URL field. Its form is “https://domain:port/dispatch”. A sample is shown below.
The port number corresponds to a named service endpoint configured on the given target host. For example, the primary compute node usually is set to have a ‘main’ service. That service’s configuration is defined in the ˜./qarbine.service/config/service.main.json file. Inside that file the following driver entry is required when using the MySQL interaction.
. . .
"./driver/microsoftSqlDriver.js"
]
The relevant configuration file name for non primary (main) Qarbine compute nodes is service.NAME.json. Remember to have well formed JSON syntax or a startup error is likely to occur. If you end up adding that entry then restart the service via the general command line syntax
For example,
or simply
Data Service Definition
Open the Administration Tool.
Navigate to the Data Services tab.
A data service defines on what compute node a query will run by default along with the means to reach to target data. The latter includes which native driver to use along with settings corresponding to that driver. Multiple Data Sources can reference a single Data Service. The details of any one Data Service are thus maintained in one spot and not spread out all over the place in each Data Source. The latter is a maintenance and support nightmare.
To begin adding a data service click
On the right hand side enter a name and optionally a description.
Set the Compute URL field based on the identified compute node above. Its form is “https://domain:port/dispatch”. A sample is shown below.
Using SQL Interfacing
Choose
Set the server name
Set the options
Other options include:
connectionTimeout=integer (milliseconds),
requestTimeout=integer (milliseconds),
pool.min=integer,
pool.max=integer,
pool.idleTimeoutMillis=integer,
For more information see https://www.npmjs.com/package/mssql#general-same-for-all-drivers
You can reference environment variables using the syntax %NAME%. Any strings should be quoted and the key\value pairs separated by commas.
Test your settings by clicking on the toolbar image highlighted below.
A sample result is shown below with the SQL version information.
Save the Data Service by clicking on the image highlighted below.
The data service will be known at the next log on time. Next, see the SQL query interaction and any tutorial for information on interacting with Azure SQL from Qarbine.